package selef.xiafan.dao;

import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import selef.xiafan.entity.order.Order;

import java.time.LocalDate;
import java.util.List;

public interface OrderDao extends JpaRepository<Order, String> {

    @Query(value = "SELECT COUNT(*) FROM t_order", nativeQuery = true)
    Long countAll();

    @Query(value = "SELECT * FROM t_order ORDER BY status LIMIT :page,:size", nativeQuery = true)
    List<Order> findAllNoQuery(@Param("page") Integer page, @Param("size") Integer size);

    @Query(value = "SELECT * FROM t_order ot LEFT JOIN t_order_customer toc " +
            "ON ot.order_id = toc.oid LEFT JOIN t_customer ct " +
            "ON toc.cid = ct.id WHERE ct.id =:id AND ot.status = :status LIMIT :page,:size", nativeQuery = true)
    Order findWithQuery(@Param("page") Integer page, @Param("size") Integer size, @Param("id") Integer id, @Param("status") Integer status);

    /**
     * @param page 页数
     * @param size 页面数量
     * @param id 客户id
     * @return
     */
    @Query(value = "SELECT * FROM t_order ot LEFT JOIN t_order_customer toc " +
            "ON ot.order_id = toc.oid LEFT JOIN t_customer ct " +
            "ON toc.cid = ct.id WHERE ct.id =:id LIMIT :page,:size", nativeQuery = true)
    Order findWithQueryNoStatus(@Param("page") Integer page, @Param("size") Integer size, @Param("id") Integer id);

    Order findByOrderId(String orderId);

    List<Order> findAllByOrderIdIn(List<String> orderId);

    void deleteByOrderId(String orderId);

    /* ******************************* 维修订单 ******************************* */
    @Query(value = "SELECT * FROM t_order WHERE status >= 2 AND status<5 ORDER BY status LIMIT :page,:size", nativeQuery = true)
    List<Order> findAllItemOrder(@Param("page") Integer page, @Param("size") Integer size);

    @Query(value = "SELECT COUNT(*) FROM t_order where status >= 2 AND status<5", nativeQuery = true)
    Long itemCountAll();

    /* ******************************* 归还订单 ******************************* */
    @Query(nativeQuery = true, value = "SELECT COUNT(*) FROM t_order WHERE status=5")
    Long countFinalList();

    @Query(nativeQuery = true, value = "SELECT * FROM t_order WHERE status=5 limit :page,:size")
    List<Order> findAllByPageQuery(@Param("page") Integer page, @Param("size") Integer size);

    /*客户订单*/
    @Query(nativeQuery = true, value = "SELECT * FROM t_order WHERE create_by = :createBy LIMIT :page,:size")
    List<Order> findAllWithQuery(@Param("page") Integer page, @Param("size") Integer size, @Param("createBy") String createBy);

    @Query(nativeQuery = true, value = "select * from t_order where create_time >= date(now()) and create_time < DATE_ADD(date(now()),INTERVAL 1 DAY)")
    List<Order> getDayOrder();

//    @Query(nativeQuery = true, value = "SELECT *, COUNT(*) AS total, (SELECT DAYOFWEEK(DATE_FORMAT(create_time,'%Y-%m-%d'))-1) AS days " +
//            "FROM t_order WHERE create_time>(SELECT subdate(curdate(),date_format(curdate(),'%w')-1)) " +
//            "AND create_time<(SELECT subdate(curdate(),date_format(curdate(),'%w')-7)) GROUP BY days ORDER BY days")
//    List<Order> queryWeekData();
}
